package com.campus.server.dao;

import com.campus.common.util.DBUtil;
import com.campus.common.vo.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    // 获取所有书籍
    public List<Book> listAllBooks() {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                books.add(new Book(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getBoolean("available"),
                        rs.getInt("stock"),
                        rs.getInt("time_limit"),
                        rs.getString("category")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }
    // 获取所有书籍类别
    public List<String> getAllCategories() {
        List<String> categories = new ArrayList<>();
        String sql = "SELECT DISTINCT category FROM book WHERE category IS NOT NULL";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                categories.add(rs.getString("category"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return categories;
    }

    // 根据类别查询书籍
    public List<Book> searchBooksByCategory(String category) {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE category = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, category);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                books.add(new Book(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getBoolean("available"),
                        rs.getInt("stock"),
                        rs.getInt("time_limit"),
                        rs.getString("category")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }
    // 借书操作
    public boolean borrowBook(int bookId) {
        String sql = "UPDATE book SET stock = stock - 1, available = (stock > 1) WHERE id = ? AND available = true";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, bookId);
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 还书操作
    public boolean returnBook(int bookId) {
        String sql = "UPDATE book SET stock = stock + 1, available = true WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, bookId);
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    public boolean borrowBook(int bookId, String username) {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);

            // 获取书籍信息（包含timeLimit）
            Book book = getBookById(bookId, conn);
            if (book == null || !book.canBorrow()) {
                conn.rollback();
                return false;
            }
            System.out.println("DEBUG: Borrowing book, id=" + bookId + ", timeLimit=" + book.getTimeLimit());
            // 更新库存
            String updateSql = "UPDATE book SET stock = stock - 1 WHERE id = ? AND stock > 0";
            try (PreparedStatement stmt = conn.prepareStatement(updateSql)) {
                stmt.setInt(1, bookId);
                int rowsAffected = stmt.executeUpdate();
                if (rowsAffected == 0) {
                    conn.rollback();
                    return false;
                }
            }

            // 如果库存为0，更新可用状态
            if (book.getStock() == 1) {
                String updateAvailableSql = "UPDATE book SET available = false WHERE id = ?";
                try (PreparedStatement stmt = conn.prepareStatement(updateAvailableSql)) {
                    stmt.setInt(1, bookId);
                    stmt.executeUpdate();
                }
            }

            // 添加借阅记录（传递timeLimit）
            BorrowRecordDao recordDao = new BorrowRecordDao();
            // 确保使用从数据库获取的book对象，而不是参数中的bookId
            boolean recordSuccess = recordDao.addBorrowRecord(username, book.getId(),
                    book.getTitle(), book.getAuthor(), book.getTimeLimit(), conn);

            if (!recordSuccess) {
                conn.rollback();
                return false;
            }

            conn.commit();
            return true;
        } catch (Exception e) {
            if (conn != null) {
                try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
            }
            e.printStackTrace();
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) { e.printStackTrace(); }
            }
        }
    }


    public boolean returnBook(int bookId, String username) {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);

            System.out.println("DEBUG: 开始还书操作 - bookId: " + bookId + ", username: " + username);

            // 1. 直接更新书籍库存和状态（原子操作）
            String updateSql = "UPDATE book SET " +
                    "stock = stock + 1, " +
                    "available = CASE WHEN (stock + 1) > 0 THEN true ELSE available END " +
                    "WHERE id = ?";

            try (PreparedStatement stmt = conn.prepareStatement(updateSql)) {
                stmt.setInt(1, bookId);
                int rowsAffected = stmt.executeUpdate();
                System.out.println("DEBUG: 更新书籍库存影响行数: " + rowsAffected);

                if (rowsAffected == 0) {
                    System.out.println("DEBUG: 书籍不存在 - 回滚事务");
                    conn.rollback();
                    return false;
                }
            }

            // 2. 获取书籍信息（使用事务内最新数据）
            Book book = getBookById(bookId, conn);
            System.out.println("DEBUG: 还书操作中查询到的书籍信息 - " +
                    (book != null ?
                            "id: " + book.getId() + ", title: " + book.getTitle() +
                                    ", author: " + book.getAuthor() :
                            "书籍不存在"));

            if (book == null) {
                System.out.println("DEBUG: 书籍不存在 - 回滚事务");
                conn.rollback();
                return false;
            }

            // 3. 删除借阅记录 - 优先使用book_id
            BorrowRecordDao recordDao = new BorrowRecordDao();
            boolean recordRemoved = recordDao.removeBorrowRecordByBookId(username, bookId);
            System.out.println("DEBUG: 通过book_id删除借阅记录结果: " + recordRemoved);

            // 如果通过book_id删除失败，尝试通过书名和作者删除
            if (!recordRemoved) {
                System.out.println("DEBUG: 尝试通过书名和作者删除借阅记录");
                recordRemoved = recordDao.removeBorrowRecord(username, book.getTitle(), book.getAuthor());
                System.out.println("DEBUG: 通过书名作者删除借阅记录结果: " + recordRemoved);
            }

            if (!recordRemoved) {
                System.out.println("DEBUG: 没有找到借阅记录 - 回滚事务");
                conn.rollback();
                return false;
            }

            conn.commit();
            System.out.println("DEBUG: 还书操作成功完成");
            return true;
        } catch (Exception e) {
            System.out.println("DEBUG: 还书操作异常: " + e.getMessage());
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    // 新增书籍
    public boolean addBook(Book book) {
        // 修改SQL语句，添加category字段
        String sql = "INSERT INTO book (title, author, available, stock, time_limit, category) VALUES (?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, book.getTitle());
            stmt.setString(2, book.getAuthor());
            stmt.setBoolean(3, book.isAvailable());
            stmt.setInt(4, book.getStock());
            stmt.setInt(5, book.getTimeLimit());
            stmt.setString(6, book.getCategory());  // 添加category参数

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除书籍
    public boolean deleteBook(int bookId) {
        String sql = "DELETE FROM book WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, bookId);
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 根据ID查询书籍
    public Book getBookById(int bookId, Connection conn) throws SQLException {
        String sql = "SELECT * FROM book WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, bookId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return new Book(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getBoolean("available"),
                        rs.getInt("stock"),
                        rs.getInt("time_limit"),
                        rs.getString("category")
                );
            }
        }
        return null;
    }

    // 根据书名查询书籍（模糊查询）
    public List<Book> searchBooksByTitle(String title) {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE title LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + title + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                books.add(new Book(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getBoolean("available"),
                        rs.getInt("stock"),
                        rs.getInt("time_limit"),
                        rs.getString("category")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    // 根据作者查询书籍（模糊查询）
    public List<Book> searchBooksByAuthor(String author) {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE author LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + author + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                books.add(new Book(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getBoolean("available"),
                        rs.getInt("stock"),
                        rs.getInt("time_limit"),
                        rs.getString("category")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    // 综合查询（根据书名和作者模糊查询）
    public List<Book> searchBooks(String title, String author) {
        List<Book> books = new ArrayList<>();

        // 构建动态SQL
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM book WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sqlBuilder.append(" AND title LIKE ?");
            params.add("%" + title + "%");
        }

        if (author != null && !author.trim().isEmpty()) {
            sqlBuilder.append(" AND author LIKE ?");
            params.add("%" + author + "%");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sqlBuilder.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                books.add(new Book(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getBoolean("available"),
                        rs.getInt("stock"),
                        rs.getInt("time_limit"),
                        rs.getString("category")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    public boolean updateBook(Book book) {
        // 修改SQL语句，添加category字段
        String sql = "UPDATE book SET title = ?, author = ?, available = ?, stock = ?, time_limit = ?, category = ? WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, book.getTitle());
            stmt.setString(2, book.getAuthor());
            stmt.setBoolean(3, book.isAvailable());
            stmt.setInt(4, book.getStock());
            stmt.setInt(5, book.getTimeLimit());
            stmt.setString(6, book.getCategory());  // 添加category参数
            stmt.setInt(7, book.getId());

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 检查书籍是否存在
    public boolean bookExists(int bookId) {
        String sql = "SELECT COUNT(*) FROM book WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, bookId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 获取用户借阅记录
    public List<Book> getUserBorrowedBooks(String username) {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT book_id, book_title, book_author FROM borrow_records WHERE username = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, username);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                books.add(new Book(
                        rs.getInt("book_id"),  // 使用实际的book_id
                        rs.getString("book_title"),
                        rs.getString("book_author"),
                        false, // 可用状态不重要
                        0,
                        0,
                        ""
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    // 添加借阅记录
    public boolean addBorrowRecord(String username, String title, String author) {
        String sql = "INSERT INTO borrow_records (username, book_title, book_author) VALUES (?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, username);
            stmt.setString(2, title);
            stmt.setString(3, author);

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除借阅记录
    public boolean removeBorrowRecord(String username, String title, String author) {
        String sql = "DELETE FROM borrow_records WHERE username = ? AND book_title = ? AND book_author = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, username);
            stmt.setString(2, title);
            stmt.setString(3, author);

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}